﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SqlDependencySample
{
    public partial class MainForm : Form
    {
        private static readonly string connectionString = ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString;
        private delegate void DataChangedEventDelegate();
        private event DataChangedEventDelegate DataChangedEvent;
        private DataTable dt;

        public MainForm()
        {
            InitializeComponent();

            EnableCrossThreadCalls();

            DataChangedEvent += ShowDataChangeMessage;

            StartSqlDependency();

            MessagesTextBox.Text += "Start Tracking...\n";
        }

        private void EnableCrossThreadCalls()
        {
            Control.CheckForIllegalCrossThreadCalls = false;
        }

        private void StartSqlDependency()
        {
            SqlDependency.Stop(connectionString);
            SqlDependency.Start(connectionString);

            GetData();
        }

        private void GetData()
        {
            string sql = "Select Id,Content From Logs";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Notification = null;
                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += OnChange;

                    using (SqlDataAdapter da = new SqlDataAdapter(command))
                    {
                        DataSet ds = new DataSet();
                        da.Fill(ds);

                        dt = ds.Tables[0];
                    }
                }
            }
        }

        void OnChange(object sender, SqlNotificationEventArgs e)
        {
            SqlDependency dependency = sender as SqlDependency;

            dependency.OnChange -= OnChange;

            if (DataChangedEvent != null)
            {
                DataChangedEvent();
            }
        }

        private void ShowDataChangeMessage()
        {
            GetData();

            MessagesTextBox.Text += "Data Changed! Count:" + dt.Rows.Count + "\n";
        }

        private void MainForm_FormClosed(object sender, FormClosedEventArgs e)
        {
            SqlDependency.Stop(connectionString);
        }
    }
}
